package dao.impl;

import dao.BaseDao;
import dao.GoodsDao;
import entity.Goods;
import vo.FlowVo;
import vo.SalesVolumeVo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class GoodsDaoImpl extends BaseDao implements GoodsDao {
    @Override
    public List<Goods> selectAll(String sql,int pageNo, int pageSize) {
        ResultSet rs=null;
        Connection conn=null;
        PreparedStatement ps=null;
        List<Goods> list=new ArrayList<>();
        try {
            rs=exeQuery(sql, new Object[]{
                    ((pageNo-1)*pageSize),
                    pageSize
            },conn,ps);
            while (rs.next()){
                Goods gs=new Goods();
                gs.setGoodsID(rs.getInt("goodsID"));
                gs.setGoodsName(rs.getString("goodsName"));
                gs.setDescribe(rs.getString("describe"));
                gs.setGoodsTime(rs.getString("goodsTime"));
                gs.setPrice(rs.getDouble("price"));
                gs.setInventory(rs.getInt("inventory"));
                gs.setFilePath(rs.getString("file_path"));
                gs.setPicturename(rs.getString("picturename"));
                gs.setState(rs.getInt("state"));
                gs.setUnits(rs.getString("units"));
                list.add(gs);
            }

        } catch (Exception exception) {
            exception.printStackTrace();
        }finally {
            closeAll(rs,ps,conn);
        }
        return list;
    }

    @Override
    public int selectcount(String sql) {
        ResultSet rs=null;
        Connection conn=null;
        PreparedStatement ps=null;
        try {
            rs=exeQuery(sql,null,conn,ps);
            while (rs.next()){
               return rs.getInt(1);
            }

        } catch (Exception exception) {
            exception.printStackTrace();
        }finally {
            closeAll(rs,ps,conn);
        }
        return 0;
    }

    /**
     * 新增商品信息
     * @param goods
     * @return
     */
    @Override
    public int goodsinsert(Goods goods) {
        String sql="INSERT INTO goods(`goodsName`, `describe`, `goodsTime`, `price`, `inventory`, `file_path`, `picturename`, `units`,`classifys`) VALUES (?, ?, ?, ?, ?, ?, ?, ?,?)";
        return exeUpdate(sql,new Object[]{
                goods.getGoodsName(),
                goods.getDescribe(),
                goods.getGoodsTime(),
                goods.getPrice(),
                goods.getInventory(),
                goods.getFilePath(),
                goods.getPicturename(),
                goods.getUnits(),
                goods.getClassifys()
        });
    }

    @Override
    public int delect(int state,String time,int id) {
        String sql="UPDATE `yipinpindb`.`goods` SET `goodsTime` = ?, `state` = ? WHERE `goodsID` = ?";
        return exeUpdate(sql,new Object[]{
                time,
                state,
                id
        });
    }

    @Override
    public Goods selectOne(int id) {
        String sql ="SELECT * FROM `yipinpindb`.`goods` WHERE `goodsID` = ?";
        ResultSet rs=null;
        Connection conn=null;
        PreparedStatement ps=null;
        Goods gs=null;
        try {
            rs=exeQuery(sql, new Object[]{
                    id
            },conn,ps);
            while (rs.next()){
                gs=new Goods();
                gs.setGoodsID(rs.getInt("goodsID"));
                gs.setGoodsName(rs.getString("goodsName"));
                 gs.setDescribe(rs.getString("describe"));
                gs.setGoodsTime(rs.getString("goodsTime"));
                gs.setPrice(rs.getDouble("price"));
                gs.setInventory(rs.getInt("inventory"));
                gs.setFilePath(rs.getString("file_path"));
                gs.setPicturename(rs.getString("picturename"));
                gs.setState(rs.getInt("state"));
                gs.setUnits(rs.getString("units"));
                gs.setClassifys(rs.getInt("classifys"));
            }
        } catch (Exception exception) {
            exception.printStackTrace();
        }finally {
            closeAll(rs,ps,conn);
        }
        return gs;
    }

    /**
     * 删除
     * @param id
     * @return
     */
    @Override
    public int goodsdelect(int id) {
        String sql="DELETE FROM `yipinpindb`.`goods` WHERE `goodsID` = ?";
        return exeUpdate(sql,new Object[]{
                id
        });
    }

    @Override
    public int update(Goods gs) {
        String sql="UPDATE `yipinpindb`.`goods` SET " +
                "`goodsName` = ?, `describe` = ?, `goodsTime` = ?," +
                " `price` = ?, `inventory` = ?, `file_path` = ?, `picturename` = ?, `units` = ?,`classifys`=? WHERE `goodsID` = ?";
        return exeUpdate(sql,new Object[]{
                gs.getGoodsName(),
                gs.getDescribe(),
                gs.getGoodsTime(),
                gs.getPrice(),
                gs.getInventory(),
                gs.getFilePath(),
                gs.getPicturename(),
                gs.getUnits(),
                gs.getClassifys(),
                gs.getGoodsID()
        });
    }

    @Override
    public List<SalesVolumeVo> selectSum() {
        String sql="\n" +
                "SELECT g.goodsName as 商品名称 , sum(o2.money) as 销售额 FROM orders o1 INNER JOIN order_info o2 on o1.orderID=o2.ordersID " +
                "INNER JOIN goods g on g.goodsID=o2.goodsID   where MONTH(payment_time)=MONTH(NOW()) GROUP BY g.goodsName  LIMIT 0,10";
        ResultSet rs=null;
        Connection conn=null;
        PreparedStatement ps=null;
        List<SalesVolumeVo> list=new ArrayList<>();
        try {
            rs=exeQuery(sql, null,conn,ps);

            while (rs.next()){
                SalesVolumeVo gs=new SalesVolumeVo();
                gs.setGoodsName(rs.getString(1));
                gs.setTotal(rs.getDouble(2));
                list.add(gs);
            }

        } catch (Exception exception) {
            exception.printStackTrace();
        }finally {
            closeAll(rs,ps,conn);
        }
        return list;
    }

    @Override
    public List<FlowVo> selectFlow() {
        String sql="select  MONTH(o1.payment_time) as 月份 ,  sum(upmoney) as 销售额 from orders o1 INNER JOIN order_info o2 on o1.orderID=o2.ordersID " +
                "INNER JOIN goods g on g.goodsID=o2.goodsID   where YEAR(payment_time)=YEAR(NOW()) GROUP BY  MONTH(o1.payment_time) ";
        ResultSet rs=null;
        Connection conn=null;
        PreparedStatement ps=null;
        List<FlowVo> list=new ArrayList<>();
        try {
            rs=exeQuery(sql, null,conn,ps);

            while (rs.next()){
                FlowVo f=new FlowVo();
               f.setMonths(rs.getString(1));
               f.setSalesVolume(rs.getInt(2));
               list.add(f);
            }

        } catch (Exception exception) {
            exception.printStackTrace();
        }finally {
            closeAll(rs,ps,conn);
        }
        return list;
    }
}